﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
//using Oracle.DataAccess.Client;
using System.Configuration;
//using System.Data.Entity.Infrastructure;
using System.ComponentModel.DataAnnotations;
using DslamPort.Models;
using System.Collections.ObjectModel;
using System.Web.Mvc;
using System.Data.OracleClient;
using System.Data;

namespace DslamPort.Models
{
    public class ReportModel
    {
        public string id { get; set; }

        public string system_id { get; set; }
        public string name { get; set; }
        public string ip { get; set; }
        public string type_name { get; set; }
        public string sum_port_use { get; set; }
        public string sum_port_not_use { get; set; }
        public string sum_port_drop { get; set; }
        public string sum_two_port { get; set; }
        public string ten_don_vi { get; set; }
        public string quan_huyen_id { get; set; }
        public List<ReportModel> getDslamPort(string str = "", string id = "")
        {
            List<ReportModel> DslamPort = new List<ReportModel>();
            string sql = "";
            if (str != "")
            {
                sql += "select quan_huyen_id, ten_don_vi,system_id,name,ip,type_name,decode(sum(column0),null,0,sum(column0)) trong,decode(sum(column1),null,0,sum(column1)) sudung,decode(sum(column2),null,0,sum(column2)) hong,sum(istwoport) sum_two_port from(";
                sql += " select a.*,decode(a.int_acc+a.mytv_acc,2,1,0) isTwoPort from( SELECT a.*,b.name,b.ip,d.name type_name,c.quan_huyen_id quan_huyen_id,c.ten ten_don_vi,decode(a.status,0,1) column0,  decode(a.status,1,1) column1,  decode(a.status,2,1) column2";
                sql += " ,decode(a.account_int,null,0,1) int_acc,decode(a.account_mytv,null,0,1) mytv_acc FROM dslam_port a,dslam b, quan_huyen c,dslam_type d  where a.system_id=b.system_id and d.id=b.dslam_type_id and b.quan_huyen_id=c.quan_huyen_id " + str + " ) a)group by quan_huyen_id, ten_don_vi,system_id,name,ip,type_name order by ten_don_vi,system_id,name";
            }
            else
                sql = "select * from quan_huyen where quan_huyen_id=0";
            var odr = BaseClass.Lib_GetDataReader(sql);
            foreach (DataRow item in odr.Rows)
            {
                DslamPort.Add(new ReportModel
                {
                    quan_huyen_id = item["quan_huyen_id"].ToString(),
                    ten_don_vi = item["ten_don_vi"].ToString(),
                    system_id = item["system_id"].ToString(),
                    name = item["name"].ToString(),
                    ip = item["ip"].ToString(),
                    type_name = item["type_name"].ToString(),
                    sum_port_use = item["sudung"].ToString(),
                    sum_port_not_use = item["trong"].ToString(),
                    sum_two_port = item["sum_two_port"].ToString(),
                    sum_port_drop = item["hong"].ToString()
                });

            }
            
            BaseClass.conn_global.Close();
            return DslamPort;
        }

        public void DeleteDslamPort(string id)
        {
            string sql = "delete from dslam_port where id=" + id;
            BaseClass.Lib_ExecuteNonQuery(sql);
        }
        //public void addDslam(ReportModel dslam)
        //{
        //    string sql = "INSERT INTO dslam";
        //    sql += " select nvl((select max(id)+1 from dslam),1),'" + dslam.system_id + "','" + dslam.name + "'," + dslam.dslam_type_id + "," + dslam.quan_huyen_id + ","+dslam.status+" from dual";
        //    BaseClass.Lib_ExecuteNonQuery(sql);
        //}


    }


}
